﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.IO;
using System.Text;
using TRL.TRLAdmin;
using TRL.DataAccessLayer;


namespace TRL.TRLAdmin.Members
{
    public partial class ViewMember : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                bindMembershipsTableToGrid();
        }

        /// <summary>
        /// Send the user a CSV file of the membership table filtered to the criteria specified.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void DownloadCSV_Click(object sender, EventArgs e)
        {
            var dt = getMembershipsDataTable();
            var fn = Path.GetTempFileName();
            using (var ffw = new FlatFileWriter(File.CreateText(fn), ',', '"', "\n"))
            {
                // Add the header
                foreach (DataColumn col in dt.Columns)
                {
                    ffw.WriteField(col.ColumnName);
                }

                ffw.WriteRecordEnd();                

                // Iterate through the datatable adding each column
                foreach (DataRow row in dt.Rows)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        ffw.WriteField( row[col].ToString() );
                    }
                    ffw.WriteRecordEnd();
                }
            }

            Response.Clear();
            Response.ClearHeaders();
            Response.ContentType = "text/csv";
            Response.AddHeader("Content-Disposition", "attachment; filename=TRLMembershipExport.csv");
            Response.WriteFile(fn);
            Response.End();
            Context.ApplicationInstance.CompleteRequest();
        }

        /// <summary>
        /// Display the membership table filtered based on the users' selected criteria
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void ViewMemberships_Click(object sender, EventArgs e)
        {
            bindMembershipsTableToGrid();
        }

        /// <summary>
        /// Beinds the membership table to the grid. We could use Linq to SQL or Linq to Entities or
        /// some other less code intensive way, but I miss the control of SqlConnection \ SqlCommand
        /// so I'm using that instead.
        /// </summary>
        private void bindMembershipsTableToGrid()
        {
            MembershipGrid.DataSource = getMembershipsDataTable();
            MembershipGrid.DataBind();

            if (MembershipGrid.Rows.Count > 0)
            {
                //This replaces <td> with <th> and adds the scope attribute
                MembershipGrid.UseAccessibleHeader = true;

                //This will add the <thead> and <tbody> elements
                MembershipGrid.HeaderRow.TableSection = TableRowSection.TableHeader;

                //This adds the <tfoot> element. 
                //Remove if you don't have a footer row
                MembershipGrid.FooterRow.TableSection = TableRowSection.TableFooter;
            }
        }

        /// <summary>
        /// Creates and returns a DataTable object of the Memberships table
        /// based on the criteria specified.
        /// 
        /// Should I have used Linq to SQL or Linq to Entities here instead? Maybe.
        /// I used a SqlCommand as that's what I knew best and felt would be the fastest.
        /// If server-side sorting is ever needed, I should probably changed to a Linq
        /// and use ExecuteQuery method
        /// </summary>
        /// <returns>A DataTable containing the Memberships table</returns>
        private DataTable getMembershipsDataTable()
        {
            string strSQLconnection = ConfigurationManager.ConnectionStrings["trladminConnectionString"].ToString();
            using (var sqlConnection = new SqlConnection(strSQLconnection))
            {
                var sqlCommandStr = @"
                            SELECT 
                                [membershipId]
                                ,mt.[typeName] AS membershipType
                                ,CONVERT(CHAR(10), [membershipExpiration], 101) AS membershipExpiration
                                ,[firstName]
                                ,[lastName]
                                ,[secondFirstName]
                                ,[secondLastName]
                                ,[address]
                                ,[city]
                                ,[state]
                                ,[postalCode]
                                ,(CASE 
                                    WHEN phoneNumber LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
                                    THEN LEFT(phoneNumber,3)+'.'+SUBSTRING(phoneNumber,4,3)+'.'+RIGHT(phoneNumber,4) 
                                    ELSE phoneNumber 
                                END) AS phoneNumber
                                ,[email]
                                ,[secondEmail]    
                                ,[forumUsername]
                                ,CONVERT(CHAR(10), [memberSince], 101) AS memberSince
                                ,[notes]
                                ,(CASE WHEN ISNULL(sendENewsletter,1) = 1 THEN 'Y' ELSE 'N' END) AS ENewsletter
                                ,(CASE WHEN ISNULL(okayToContact,1) = 1 THEN 'Y' ELSE 'N' END) AS Contact
                                ,[dob] AS DOB
                                ,[secondDob] AS 'Second Person DOB'
                                ,[occupation] AS 'Occupation'
                                ,[secondOccupation] AS 'Second Person Occupation'
                                ,[gender] AS 'Gender'
                                ,[secondGender] AS 'Second Person Gender'
                                ,[shirtSize] AS 'Shirt Size'
                                ,[secondShirtSize] AS 'Second Person Short'
                            FROM Memberships m
                            INNER JOIN MembershipTypes mt ON
                                mt.membershipTypeId = m.membershipType
                            ";
                var sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                var whereClause = "";
                if (Current.Checked == true)
                {
                    whereClause = "WHERE membershipExpiration > GETDATE()";
                }
                else if (Expiration.Checked == true)
                {
                    whereClause = "WHERE ";
                    if (expStartDate.Text != "" && expEndDate.Text != "")
                    {
                        var date1 = DateTime.Parse(expStartDate.Text);
                        var date2 = DateTime.Parse(expEndDate.Text);
                        if (date1 < date2)
                        {
                            sqlCommand.Parameters.AddWithValue("@expStartDate", date1);
                            sqlCommand.Parameters.AddWithValue("@expEndDate", date2);
                        }
                        else
                        {
                            sqlCommand.Parameters.AddWithValue("@expStartDate", date2);
                            sqlCommand.Parameters.AddWithValue("@expEndDate", date1);
                        }
                        whereClause += "membershipExpiration BETWEEN @expStartDate AND @expEndDate";
                    }
                    else
                    {
                        // If startDate or endDate values wasn't entered it means the Javascript
                        // that sets these based on the dropdown isn't functioning. Still try
                        // to display expected results by checking dropdown. In theory
                        // this should never happen
                        switch (ExpSelector.SelectedValue)
                        {
                            case "Any":
                                whereClause += "membershipExpiration < GETDATE()";
                                break;
                            case "LastMonth":
                                whereClause += "membershipExpiration BETWEEN DATEADD(m,-1,GETDATE()) AND GETDATE()";
                                break;
                            case "LastTwoMonths":
                                whereClause += "membershipExpiration BETWEEN DATEADD(m,-2,GETDATE()) AND GETDATE()";
                                break;
                            case "LastFourMontsh":
                                whereClause += "membershipExpiration BETWEEN DATEADD(m,-4,GETDATE()) AND GETDATE()";
                                break;
                            case "NextMonth":
                                whereClause += "membershipExpiration BETWEEN GETDATE() AND DATEADD(m,1,GETDATE())";
                                break;
                            case "NextTwoMonths":
                                whereClause += "membershipExpiration BETWEEN GETDATE() AND DATEADD(m,2,GETDATE())";
                                break;
                            case "NextFourMonths":
                                whereClause += "membershipExpiration BETWEEN GETDATE() AND DATEADD(m,4,GETDATE())";
                                break;
                            case "Between":
                                throw new Exception("Between was choosen in the selector but a start date and end date weren't entered.");
                            default:
                                throw new Exception("Unrecognized expiration criteria selection");
                        }
                    }
                }


                sqlCommandStr += whereClause + "\nORDER BY membershipExpiration DESC";
                sqlCommand.CommandText = sqlCommandStr;
                sqlConnection.Open();

                var reader = sqlCommand.ExecuteReader();
                var dt = new DataTable();
                dt.Load(reader);
                return dt;
            }
        }
    }

}


